![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
The initialization parameter DB_BLOCK_BUFFERS specifies the number of database block buffers configured in the system. The total amount of space utilized by these buffers is calculated as follows: Buffer Size = DB_BLOCK_BUFFERS * DB_BLOCK_SIZE
You can obtain an estimate of additional cache hits that would be achieved with more database block buffers through the virtual table X$KCBRBH. When this table is enabled through the Oracle initialization parameter DB_BLOCK_LRU_EXTENDED_STATISTICS, it contains estimates of the performance of a larger buffer cache. The parameter DB_BLOCK_LRU_EXTENDED_STATISTICS specifies the number of rows in the X$KCBRBH table. Each row contains the estimated cache hits obtained by those additional buffers. The table X$KCBRBH contains the following columns:
Be sure to run your application while you are gathering these statistics. Doing so gives you a better representation of how your particular system will react with more database block buffers. Because the data files and the database block buffers make up the majority of the I/Os in the system, it is important to have a sufficiently large buffer cache. Dont increase your database block buffers at the expense of the shared pool. Although the shared pool is not as large or as heavily used, it serves a critical purpose in the execution of SQL statements. Any time you make a significant change in DB_BLOCK_BUFFERSor any Oracle parametergo back and check the OS for different I/O rates. Also check within Oracle to see whether the cache-hit rates have changed, as well as the I/O rates. Tuning the I/O SubsystemI/O is probably one of the most common problems facing Oracle users. In many cases, the performance of the system is entirely limited by disk I/O. In some cases, the system actually becomes idle waiting for disk requests to complete. We say that these systems are I/O bound or disk bound. As you see in Chapter 14, Advanced Disk I/O Concepts, disks have certain inherent limitations that cannot be overcome. Therefore, the way to deal with disk I/O issues is to understand the limitations of the disks and design your system with these limitations in mind. Knowing the performance characteristics of your disks can help you in the design stage. Optimizing your system for I/O should happen during the design stage. As you see in Part III, Configuring the System, different types of systems have different I/O patterns and require different I/O designs. Once the system is built, you should first tune for memory and then tune for disk I/O. The reason you tune in this order is to make sure that you are not dealing with excessive cache misses, which cause additional I/Os. The strategy for tuning disk I/O is to keep all drives within their physical limits. Doing so reduces queuing timeand thus increases performance. In your system, you may find that some disks process many more I/Os per second than other disks. These disks are called hot spots. Try to reduce hot spots whenever possible. Hot spots occur whenever there is a lot of contention on a single disk or set of disks. Understanding Disk ContentionDisk contention occurs whenever the physical limitations of a disk drive are reached and other processes have to wait. Disk drives are mechanical and have a physical limitation on both disk seeks per second and throughput. If you exceed these limitations, you have no choice but to wait. You can find out if you are exceeding these limits both through Oracles file I/O statistics and through operating system statistics. This chapter looks at the Oracle statistics; Chapter 12, Operating System-Specific Tuning, looks at the operating system statistics for some popular systems. Although the Oracle statistics give you an accurate picture of how many I/Os have taken place for a particular data file, they may not accurately represent the entire disk because other activity outside of Oracle may be incurring disk I/Os. Remember that you must correlate the Oracle data file to the physical disk on which it resides. Information about disk accesses is kept in the dynamic performance table V$FILESTAT. Important information in this table is listed in the following columns:
The information in V$FILESTAT is referenced by file number. The dynamic performance table V$DATAFILE contains a reference to this number as well as other useful information such as this:
Together, the V$FILESTAT and V$DATAFILE tables can give you an idea of the I/O usage of your data files. Use the following query to get this information: SQL> SELECT substr(name,1,40), phyrds, phywrts, status, bytes 2 FROM v$datafile df, v$filestat fs 3 WHERE df.file# = fs.file#; SUBSTR(NAME,1,40) PHYRDS PHYWRTS STATUS BYTES ---------------------------------------- -------- -------- ------ -------- C:\UTIL\ORAWIN\DBS\wdbsys.ora 221 7 SYSTEM 10485760 C:\UTIL\ORAWIN\DBS\wdbuser.ora 0 0 ONLINE 3145728 C:\UTIL\ORAWIN\DBS\wdbrbs.ora 2 0 ONLINE 3145728 C:\UTIL\ORAWIN\DBS\wdbtemp.ora 0 0 ONLINE 2097152 The total I/O for each data file is the sum of the physical reads and physical writes. It is important to make sure that these I/Os dont exceed the physical limitations of any one disk. I/O throughput problems to one disk may slow down the entire system depending on what data is on that disk. It is particularly important to make sure that I/O rates are not exceeded on the disk drives.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |